* This file combines the SSDI ASR data tables in csv format, outputs a dta file

* The following global variable should be defined to point to the SSDI_ASR "project" folder
* global SSDI_ASR = "path/to/SSDI_ASR"
assert !missing("$SSDI_ASR")

* ------------------------------------------------------
* SSDI ASR Tables avaiable: 60, 61, 62, and 63
* ------------------------------------------------------
local table_nums 60 61 62 63

* Process each table
foreach table_num of local table_nums {
  * Specify SSDI ASR table number
  * local table_num 61
  
  * List all csv tables
  local tables_age_groups : dir "$SSDI_ASR/csv/age_groups" files "SSDI_ASR_`table_num'_*.csv"
  local tables_age_single : dir "$SSDI_ASR/csv/age_single" files "SSDI_ASR_`table_num'_*.csv"
  di `"`tables_age_groups'"'
  di `"`tables_age_single'"'
  local tables
  local levels age_groups age_single
  foreach level of local levels {
    foreach table of local tables_`level' {
      local tables `tables' csv/`level'/`table'
    }
  }
  
  * Convert individual csv tables to dta tables
  cap mkdir "$SSDI_ASR/dta"
  cap mkdir "$SSDI_ASR/dta/age_groups"
  cap mkdir "$SSDI_ASR/dta/age_single"
  local dta_tables
  foreach table of local tables {
    local dta_table = subinstr("`table'", "csv", "dta", .)
    local dta_tables `dta_tables' `dta_table'
    
    import delimited "$SSDI_ASR/`table'", delimiter(comma) varnames(1) stringcols(_all) clear
    save "$SSDI_ASR/`dta_table'", replace
  }
  
  * Combine individual dta tables into single, combined dta table
  clear
  foreach dta_table of local dta_tables {
    append using "$SSDI_ASR/`dta_table'"
  }
  
  * Numeric variables
  local varlist_60 table year *total* *decision* *denials* *allowance* *award*
  local varlist_61 table year *decision*
  local varlist_62 table year *decision*
  local varlist_63 table year *decision*
  foreach var of varlist `varlist_`table_num'' {
    di "`var'"
    capture confirm variable `var'
    if _rc > 0 continue
    capture confirm string variable `var'
    if _rc > 0 continue
    
    * Destring numeric variables, confirm that "(X)" indicates missing value
    tempvar tvar
    gen `tvar' = `var'
    destring `var', replace ignore(",") force
    assert `tvar' == "(X)" if missing(`var')
    drop `tvar'
  }
  
  * Label variables
  label var table                            "Outcomes of Applications for Disability Benefits - Table 60"
  label var group                            "Applicant type"
  label var age                              "Applicant age or age group"
  label var year                             "Year of application"
  
  if `table_num' == 60 {
    label var table                            "Outcomes of Applications for Disability Benefits - Table 60"
    label var group                            "Applicant type"
    label var age                              "Applicant age or age group"
    label var year                             "Year of application"
    label var total                            "Total"
    label var pendingfinaldecision             "Pending final decision"
    label var technicaldenials                 "Technical denials"
    label var medicaldecisionsdenialsmedical   "Medical decisions - Denials - Medical"
    label var medicaldecisionsdenialssubsequen "Medical decisions - Denials - Subsequent nonmedical"
    label var medicaldecisionsallowancesawards "Medical decisions - Allowances - Awards"
    label var medicaldecisionsallowancessubseq "Medical decisions - Allowances - Subsequent denials"
    label var awardratepercent                 "Award rate"
    label var allowanceratepercent             "Allowance rate"
  }
  
  if inrange(`table_num', 61, 63) {
    label var alldecisionstotal                "All decisions - Total"
    label var alldecisionsallowances           "All decisions - Allowances"
    label var alldecisionsallowanceratepercent "All decisions - Allowance rate (percent)"
    label var decisionsssonlytotal             "Decisions on applications for Social Security only - Total"
    label var decisionsssonlyallowances        "Decisions on applications for Social Security only - Allowances"
    label var decisionsssonlyallowancerateperc "Decisions on applications for Social Security only - Allowance rate (percent)"
    label var decisionsssssitotal              "Decisions on applications for both Social Security and SSI - Total"
    label var decisionsssssiallowances         "Decisions on applications for both Social Security and SSI - Allowances"
    label var decisionsssssiallowancerateperce "Decisions on applications for both Social Security and SSI - Allowance rate (percent)"
  }
  
  * QC
  isid group age year
  sort group age year
  
  * Save
  cap mkdir "$SSDI_ASR/final"
  save "$SSDI_ASR/final/SSDI_ASR_`table_num'.dta", replace
  
}


** EOF
